package com.kis;

import jxl.*;
import jxl.format.UnderlineStyle;
import jxl.write.Boolean;
import jxl.write.*;
import jxl.write.Number;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by yks on 15-11-27.
 */
public class Excel {


    public static List list = new ArrayList();


    /**
     * ¶ÁÈ¡Excel
     *
     * @param filePath
     */
    public static void readExcel(String filePath) {
        String data2 = "";
        //DateTime tem=new DateTime("");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            InputStream is = new FileInputStream(filePath);
            Workbook rwb = Workbook.getWorkbook(is);
            //Sheet st = rwb.getSheet("0")ÕâÀïÓÐÁœÖÖ·œ·š»ñÈ¡sheet±í,1ÎªÃû×Ö£¬¶øÎªÏÂ±ê£¬ŽÓ0¿ªÊŒ
            Sheet st = rwb.getSheet(0);
            int rs = st.getColumns();
            int rows = st.getRows();
            System.out.println("ÁÐÊý===>" + rs + "ÐÐÊý£º" + rows);


            for (int k = 0; k < rows; k++) {//ÐÐ
                for (int i = 0; i < rs; i++) {//ÁÐ

                    Cell c00 = st.getCell(i, k);
                    //ÍšÓÃµÄ»ñÈ¡cellÖµµÄ·œÊœ,·µ»Ø×Ö·ûŽ®
                    String strc00 = c00.getContents();
                    //»ñµÃcellŸßÌåÀàÐÍÖµµÄ·œÊœ
                    if (c00.getType() == CellType.LABEL) {
                        LabelCell labelc00 = (LabelCell) c00;
                        strc00 = labelc00.getString();
                    }
                    //excel ÀàÐÍÎªÊ±ŒäÀàÐÍŽŠÀí;
                    if (c00.getType() == CellType.DATE) {
                        DateCell dc = (DateCell) c00;
                        strc00 = sdf.format(dc.getDate());

                    }
                    //excel ÀàÐÍÎªÊýÖµÀàÐÍŽŠÀí;
            /*
            if(c00.getType()==CellType.NUMBER|| c00.getType()==CellType.NUMBER_FORMULA){
            	NumberCell nc=(NumberCell)c00;
            	strc00=""+nc.getValue();
            }*/

                    //Êä³ö
                    System.out.println(">" + strc00);

                    list.add(strc00);


                    //ÁÐ£¬ÐÐ
//   		 data2=String.valueOf(st.getCell(1,k).getContents());
//   		 data2=data2.replace("/", "-");
//           java.util.Date dt=sdf.parse(data2);
//           System.out.println(sdf.format(dt));
//
                }
                // System.out.println(data2+"======"+list.get(k)+"=========");
            }


            //¹Ø±Õ
            rwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Êä³öExcel
     *
     * @param os
     */
    public static void writeExcel(OutputStream os) {
        try {
            /**
             * Ö»ÄÜÍš¹ýAPIÌá¹©µÄ¹€³§·œ·šÀŽŽŽœšWorkbook£¬¶ø²»ÄÜÊ¹ÓÃWritableWorkbookµÄ¹¹Ôìº¯Êý£¬
             * ÒòÎªÀàWritableWorkbookµÄ¹¹Ôìº¯ÊýÎªprotectedÀàÐÍ
             * method(1)Ö±œÓŽÓÄ¿±êÎÄŒþÖÐ¶ÁÈ¡WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));
             * method(2)ÈçÏÂÊµÀýËùÊŸ œ«WritableWorkbookÖ±œÓÐŽÈëµœÊä³öÁ÷

             */
            WritableWorkbook wwb = Workbook.createWorkbook(os);
            //ŽŽœšExcel¹€×÷±í Öž¶šÃû³ÆºÍÎ»ÖÃ
            WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);

            //**************Íù¹€×÷±íÖÐÌíŒÓÊýŸÝ*****************

            //1.ÌíŒÓLabel¶ÔÏó
            Label label = new Label(0, 0, "this is a label test");
            ws.addCell(label);

            //ÌíŒÓŽøÓÐ×ÖÐÍFormatting¶ÔÏó
            WritableFont wf = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
            WritableCellFormat wcf = new WritableCellFormat(wf);
            Label labelcf = new Label(1, 0, "this is a label test", wcf);
            ws.addCell(labelcf);

            //ÌíŒÓŽøÓÐ×ÖÌåÑÕÉ«µÄFormatting¶ÔÏó
            WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
                    UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
            WritableCellFormat wcfFC = new WritableCellFormat(wfc);
            Label labelCF = new Label(1, 0, "This is a Label Cell", wcfFC);
            ws.addCell(labelCF);

            //2.ÌíŒÓNumber¶ÔÏó
            Number labelN = new Number(0, 1, 3.1415926);
            ws.addCell(labelN);

            //ÌíŒÓŽøÓÐformattingµÄNumber¶ÔÏó
            NumberFormat nf = new NumberFormat("#.##");
            WritableCellFormat wcfN = new WritableCellFormat(nf);
            Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
            ws.addCell(labelNF);

            //3.ÌíŒÓBoolean¶ÔÏó
            Boolean labelB = new jxl.write.Boolean(0, 2, false);
            ws.addCell(labelB);

            //4.ÌíŒÓDateTime¶ÔÏó
            jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3, new java.util.Date());
            ws.addCell(labelDT);

            //ÌíŒÓŽøÓÐformattingµÄDateFormat¶ÔÏó
            DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");
            WritableCellFormat wcfDF = new WritableCellFormat(df);
            DateTime labelDTF = new DateTime(1, 3, new java.util.Date(), wcfDF);
            ws.addCell(labelDTF);


            //ÌíŒÓÍŒÆ¬¶ÔÏó,jxlÖ»Ö§³ÖpngžñÊœÍŒÆ¬
            File image = new File("d://2.png");
            WritableImage wimage = new WritableImage(0, 1, 2, 2, image);
            ws.addImage(wimage);
            //ÐŽÈë¹€×÷±í
            wwb.write();
            wwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * ¿œ±Žºó,œøÐÐÐÞžÄ,ÆäÖÐfile1Îª±»copy¶ÔÏó£¬file2ÎªÐÞžÄºóŽŽœšµÄ¶ÔÏó
     * Ÿ¡µ¥ÔªžñÔ­ÓÐµÄžñÊœ»¯ÐÞÊÎÊÇ²»ÄÜÈ¥µôµÄ£¬ÎÒÃÇ»¹ÊÇ¿ÉÒÔœ«ÐÂµÄµ¥ÔªžñÐÞÊÎŒÓÉÏÈ¥£¬
     * ÒÔÊ¹µ¥ÔªžñµÄÄÚÈÝÒÔ²»Í¬µÄÐÎÊœ±íÏÖ
     *
     * @param file1
     * @param file2
     */
    public static void modifyExcel(File file1, File file2) {
        try {
            Workbook rwb = Workbook.getWorkbook(file1);
            WritableWorkbook wwb = Workbook.createWorkbook(file2, rwb);//copy
            WritableSheet ws = wwb.getSheet(0);
            WritableCell wc = ws.getWritableCell(0, 0);
            //ÅÐ¶Ïµ¥ÔªžñµÄÀàÐÍ,×ö³öÏàÓŠµÄ×ª»»
            if (wc.getType() == CellType.LABEL) {
                Label label = (Label) wc;
                label.setString("The value has been modified");
            }
            wwb.write();
            wwb.close();
            rwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //²âÊÔ
    public static void main(String[] args) {
        try {
            //¶ÁExcel
            Excel.readExcel("/home/yks/t.xls");
            //Êä³öExcel
//             File fileWrite = new File("/home/yks/testWrite.xls");
            //fileWrite.createNewFile();
            //OutputStream os = new FileOutputStream(fileWrite);
            // excelTest.writeExcel(os);
            //ÐÞžÄExcel
            //  excelTest.modifyExcel(new File(""),new File(""));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
